import pymysql
import pandas as pd
import redis
import trino
from trino import transaction


# env = 'online'          # 线上发布
env = 'offline'         # 线下测试

DB_CONFIG = {
    'host_hera_local': 'rm-uf60roc5i4prl89okeo.mysql.rds.aliyuncs.com',
    'host_hera_online': 'rm-uf60roc5i4prl89ok.mysql.rds.aliyuncs.com',
    'user_hera': 'ygl_arw',
    'password_hera': 'ekUdp38lSqjvtBCabz',

    'host_zfw_local': 'rr-uf65o8t63gt74y9knuo.mysql.rds.aliyuncs.com',
    'host_zfw_online': 'rr-uf65o8t63gt74y9knuo.mysql.rds.aliyuncs.com',
    'user_zfw': 'ygl_python',
    'password_zfw': 'CdEme9LJPsmHTdeu',

    'host_wfw_local': 'rm-uf692m42e075f43mano.mysql.rds.aliyuncs.com',
    'host_wfw_online': 'rr-uf66565g7jt749y30.mysql.rds.aliyuncs.com',
    'user_wfw': 'ygl_arw',
    'password_wfw': 'yl2vSivhk1qIjx26gi',
}

ONLINE_HOST_CONFIG = {
    'host_hera': DB_CONFIG['host_hera_online'],
    'host_zfw': DB_CONFIG['host_zfw_online'],
    'host_wfw': DB_CONFIG['host_wfw_online'],
}

LOCAL_HOST_CONFIG = {
    'host_hera': DB_CONFIG['host_hera_local'],
    'host_zfw': DB_CONFIG['host_zfw_local'],
    'host_wfw': DB_CONFIG['host_wfw_local'],
}


if env == 'online':
    HOST_CONFIG = ONLINE_HOST_CONFIG  # 线上发布
else:
    HOST_CONFIG = LOCAL_HOST_CONFIG     # 线下测试


conm = pymysql.connect(
    host=HOST_CONFIG['host_zfw'],
    user=DB_CONFIG['user_zfw'],
    passwd=DB_CONFIG['password_zfw'],
    port=3306, db="qby_j")


# pandas查询数据库 -> DataFrame
# 主服务
def pd_conn_zfw(sql):
    conn = pymysql.connect(host=HOST_CONFIG['host_zfw'],
                           user=DB_CONFIG['user_zfw'],
                           passwd=DB_CONFIG['password_zfw'],
                           port=3306, db="qby_j")
    df = pd.read_sql(sql, con=conn)
    conn.close()
    return df


# 微服务
def pd_conn_wfw(sql, db):
    conn = pymysql.connect(host=HOST_CONFIG['host_wfw'],
                           user=DB_CONFIG['user_wfw'],
                           passwd=DB_CONFIG['password_wfw'],
                           port=3306, db=db)
    df = pd.read_sql(sql, con=conn)
    conn.close()
    return df


# 赫拉
def pd_conn_wfw_okr_write(sql):
    conn = pymysql.connect(host=HOST_CONFIG['host_hera'],
                           user=DB_CONFIG['user_hera'],
                           passwd=DB_CONFIG['password_hera'],
                           port=3306, db="ygl_dw")
    df = pd.read_sql(sql, con=conn)
    conn.close()
    return df


# 执行SQL —— 返回 元组
def executeSql(sql):
    # 开始时间
    conn = pymysql.connect(host=HOST_CONFIG['host_zfw'],
                           user=DB_CONFIG['user_zfw'],
                           passwd=DB_CONFIG['password_zfw'],
                           port=3306, db="qby_j")
    cursor = conn.cursor()
    count = cursor.execute(sql)
    fields = cursor.description
    result = cursor.fetchall()
    cursor.close()
    conn.close()
    return fields, result


# 连接缓存数据库
def get_redis():
    r = redis.StrictRedis(host='r-uf61da01d3279ed4pd.redis.rds.aliyuncs.com',
                          port=6379,
                          db=0,
                          password="123asdASD",
                          decode_responses=True)
    return r


def get_cons():
    cons = trino.dbapi.connect(
        host=HOST_CONFIG['host_dw'],
        port=9090,
        user='root',
        catalog='hive',
        isolation_level=transaction.IsolationLevel.READ_COMMITTED
    )
    return cons